package fy.mdm.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONArray;


import fy.mdm.bean.MesCCProductInfoFindBean;
import fy.util.DBUtil;


public class MesCCProductInfoFindDao {
	private final static String sql = "select a.consult_no,a.list_num,a.req_num,a.ship_lot_number,a.customer_code,a.cuname,a.car_type,a.item_code,a.part_num,a.piecenum,a.case_num,a.packing_per_case,a.shipping_date,a.customer_address,a.contacts_person1,a.shipping_carrier,a.shipping_cartype,a.HEIGHT,a.SEAL_NUM,a.CONTAINER_NUM  from fy_ship_product_cc_v a ";
//	private final static String sql = "select a.consult_no,a.list_num,a.req_num,a.ship_lot_number,a.customer_code,a.cuname,a.car_type,a.item_code,a.part_num,a.piecenum,a.case_num,a.packing_per_case,a.shipping_date,a.customer_address,a.contacts_person1,a.shipping_carrier,a.shipping_cartype,a.HEIGHT  from fy_ship_product_cc_v a ";
	private String[] codes;//={"181218082217"};
	private static Log log = LogFactory.getLog(MesCCProductInfoFindDao.class);

	public JSONArray  findProductInfo() throws Exception {
		
		String consult_no = null;
		String list_num = null;
		String req_num = null;
		String ship_lot_number = null;
		String customer_code = null;
		String cuname = null;
		String car_type = null;
		String item_code = null;
		String part_num = null;
		String piecenum = null;
		String case_num = null;
		String packing_per_case = null;
		String shipping_date = null;
		
		String customer_address=null;
		String contacts_person1=null;
		String shipping_carrier=null;
		String shipping_cartype=null;
		String box_height=null;
		String Tank_no=null;
		String Titles=null;
		
		JSONArray jso=new JSONArray();
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		MesCCProductInfoFindBean mesCCProductInfoFindBean = new MesCCProductInfoFindBean();
	
		try {
			conn = connect("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@172.17.0.82:1521:ccrac2",
					"ext_interface", "me_re_port1");
			pst = conn.prepareStatement(getSQL());
			setParams(pst);
			rs = pst.executeQuery();
//			System.err.println("123456");
			while(rs.next()) {
//				System.err.println("123456");
				consult_no = rs.getString(1);
				list_num = rs.getString(2);
				req_num = rs.getString(3);
				ship_lot_number = rs.getString(4);
				customer_code = rs.getString(5);
				cuname = rs.getString(6);
				car_type = rs.getString(7);
				item_code = rs.getString(8);
				part_num = rs.getString(9);
				piecenum = rs.getString(10);
				case_num = rs.getString(11);
				packing_per_case = rs.getString(12);
				shipping_date = rs.getString(13);
				customer_address=rs.getString(14);
				contacts_person1=rs.getString(15);
				shipping_carrier=rs.getString(16);
				shipping_cartype=rs.getString(17);				
				
//				System.err.println("重量1："+rs.getString(18));
				box_height=rs.getString(18);
//				Tank_no=rs.getString(19);//柜号
//				Titles=rs.getString(20);//封号
//				System.out.println(consult_no + "   " + list_num + "   " + req_num + "   " + ship_lot_number + "   "
//						+ customer_code + "  " + cuname);
				
				  mesCCProductInfoFindBean.setCar_type(car_type);
				  mesCCProductInfoFindBean.setCase_num(case_num);
				  mesCCProductInfoFindBean.setConsult_no(consult_no);
	
				 mesCCProductInfoFindBean.setConsult_no(consult_no);
				 mesCCProductInfoFindBean.setCuname(cuname);
				
				  mesCCProductInfoFindBean.setCustomer_code(customer_code);
				  mesCCProductInfoFindBean.setItem_code(item_code);
				  mesCCProductInfoFindBean.setList_num(list_num);
				  mesCCProductInfoFindBean.setPacking_per_case(packing_per_case);
				 mesCCProductInfoFindBean.setPart_num(part_num);
				  mesCCProductInfoFindBean.setPiecenum(piecenum);
				 mesCCProductInfoFindBean.setReq_num(req_num);
				  mesCCProductInfoFindBean.setShip_lot_number(ship_lot_number);
				  mesCCProductInfoFindBean.setShipping_date(shipping_date);
				  
				  
				  mesCCProductInfoFindBean.setCustomer_address(customer_address);
				  mesCCProductInfoFindBean.setContacts_person1(contacts_person1);
				  mesCCProductInfoFindBean.setShipping_carrier(shipping_carrier);
				  mesCCProductInfoFindBean.setShipping_cartype(shipping_cartype);
				  
//				  System.err.println("柜号:"+Tank_no);
//				  System.err.println("封号:"+Titles);
				  mesCCProductInfoFindBean.setBox_height(box_height);
				  mesCCProductInfoFindBean.setTank_no(Tank_no);
				  mesCCProductInfoFindBean.setTitles(Titles);
				  
		
				//jo.put(mesCCProductInfoFindBean.getID(), mesCCProductInfoFindBean.getJSONObject());	

			
				jso.put(mesCCProductInfoFindBean.getJSONObject());
				
				}
				log.info("jso"+jso);
			
		//	}
	
		
		} catch (Exception f) {
			f.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		};
		return jso;

	}

	public static Connection connect(String driverClassName, String url, String userName, String password)
			throws Exception {

		Class.forName(driverClassName);
		DriverManager.setLoginTimeout(60);
		return DriverManager.getConnection(url, userName, password);
	}
	
/*	private String getSQL() {
		StringBuilder sb = new StringBuilder();

		sb.append(sql);

		for (int i = 0, length = this.codes.length; i < length; i++) {
			if (i == 0) {
				sb.append(" consult_no=? ");
			} else {
				sb.append(" or consult_no=? ");
			}
			if( this.codes.length==0){
				sb.append("");
			}else{
				sb.append("or consult_no=?");
			}
			
		}
	//	sb.append(" ) ");

		return sb.toString();
	}*/
	
	private String getSQL() {
		StringBuilder sb = new StringBuilder();

		sb.append(sql);

		for (int i = 0, length = this.codes.length; i < length; i++) {
			if (i == 0) {
				sb.append(" where ( consult_no=? ");
			} else {
				sb.append(" or consult_no=? ");
			}
		}
		sb.append("  ) order by consult_no ");

		return sb.toString(); 	
	}
	
	
	private void setParams(PreparedStatement pst) throws Exception {
	for (int i = 0, length = this.codes.length; i < length; i++) {
			
//			System.out.println("******2"+ this.codes[i]);
			pst.setString(i + 1, this.codes[i]);
	}
	}

	public void setCodes(String[] codesArray) {
		this.codes = codesArray;
		
//	for (int i = 0, length = this.codes.length; i < length; i++) {
//
//			System.out.println("******1"+ this.codes[i]);
//		}
		
	}
}
